const mysql = require('mysql2/promise');
const config = require('./database-config.js');

/**
 * 根据标准指令说明文档更新物品图片
 * 从1120和1199指令的响应数据中提取图片信息
 */

// 从标准指令说明文档中提取的物品图片映射
const ITEMS_PIC_MAPPING = {
    // 宝物商店物品 (1120指令)
    68: { name: '将军令', pic: 'jjl.png' },
    69: { name: '大将军令', pic: 'djjl.png' },
    78: { name: '护身符', pic: 'hsf.png' },
    79: { name: '青铜钥匙', pic: 'qtys.png' },
    80: { name: '白银钥匙', pic: 'byys.png' },
    81: { name: '黄金钥匙', pic: 'hjys.png' },
    82: { name: '体力丹', pic: 'tld.png' },
    85: { name: '易名神符', pic: 'ymsf.png' },
    157: { name: '原木钥匙', pic: 'ymys.png' },
    158: { name: '精钢钥匙', pic: 'jgys.png' },
    159: { name: '珍奇钥匙', pic: 'zqys.png' },
    414: { name: '兵符', pic: 'bf.png' },
    651: { name: '手里剑', pic: 'slj.png' },
    653: { name: '签到令牌', pic: 'qdlp.png' },
    655: { name: '壕客令牌', pic: '' }, // 文档中为空
    656: { name: '超级壕客令牌', pic: '' }, // 文档中为空

    // 经验商店物品 (1199指令)
    83: { name: '副将经验书', pic: 'fjjys.png' },
    147: { name: '初级副将心法', pic: 'cjfjxf.png' },
    148: { name: '高级副将心法', pic: 'gjfjxf.png' },
    149: { name: '特级副将心法', pic: 'tjfjxf.png' },
    425: { name: '密宗副将心法', pic: 'mzfjxf.png' },
    131: { name: '一级技能书', pic: '1jjns.png' },
    132: { name: '二级技能书', pic: '2jjns.png' },
    133: { name: '三级技能书', pic: '3jjns.png' },
    134: { name: '四级技能书', pic: '4jjns.png' },
    135: { name: '五级技能书', pic: '5jjns.png' }
};

async function updateItemsPicFromStandard() {
    let connection;

    try {
        console.log('🖼️ 开始根据标准指令说明更新物品图片...');

        // 连接数据库
        connection = await mysql.createConnection(config.source);
        console.log('✅ 数据库连接成功');

        // 1. 检查当前物品图片状态
        console.log('\n1️⃣ 检查当前物品图片状态...');
        const [currentStatus] = await connection.execute(`
      SELECT 
        COUNT(*) as total_items,
        COUNT(CASE WHEN pic != '' THEN 1 END) as items_with_pic,
        COUNT(CASE WHEN pic = '' THEN 1 END) as items_without_pic
      FROM basic_items
    `);

        console.log('📊 当前状态:');
        console.log(`  - 总物品数: ${currentStatus[0].total_items}`);
        console.log(`  - 有图片: ${currentStatus[0].items_with_pic}`);
        console.log(`  - 无图片: ${currentStatus[0].items_without_pic}`);

        // 2. 更新物品图片
        console.log('\n2️⃣ 更新物品图片...');
        let updatedCount = 0;
        let skippedCount = 0;

        for (const [itemId, itemInfo] of Object.entries(ITEMS_PIC_MAPPING)) {
            try {
                // 检查物品是否存在
                const [existingItem] = await connection.execute(
                    'SELECT id, name, pic FROM basic_items WHERE id = ?',
                    [itemId]
                );

                if (existingItem.length === 0) {
                    console.log(`⚠️ 物品ID ${itemId} (${itemInfo.name}) 不存在，跳过`);
                    skippedCount++;
                    continue;
                }

                const currentItem = existingItem[0];

                // 如果图片为空字符串，跳过更新
                if (itemInfo.pic === '') {
                    console.log(`⚠️ 物品ID ${itemId} (${itemInfo.name}) 图片为空，跳过`);
                    skippedCount++;
                    continue;
                }

                // 更新图片
                await connection.execute(
                    'UPDATE basic_items SET pic = ?, updated_at = NOW() WHERE id = ?',
                    [itemInfo.pic, itemId]
                );

                console.log(`✅ 更新物品ID ${itemId}: ${currentItem.name} -> ${itemInfo.pic}`);
                updatedCount++;

            } catch (error) {
                console.error(`❌ 更新物品ID ${itemId} 失败:`, error.message);
            }
        }

        console.log(`\n📊 更新统计:`);
        console.log(`  - 成功更新: ${updatedCount} 个物品`);
        console.log(`  - 跳过: ${skippedCount} 个物品`);

        // 3. 验证更新结果
        console.log('\n3️⃣ 验证更新结果...');

        // 检查更新后的状态
        const [newStatus] = await connection.execute(`
      SELECT 
        COUNT(*) as total_items,
        COUNT(CASE WHEN pic != '' THEN 1 END) as items_with_pic,
        COUNT(CASE WHEN pic = '' THEN 1 END) as items_without_pic
      FROM basic_items
    `);

        console.log('📊 更新后状态:');
        console.log(`  - 总物品数: ${newStatus[0].total_items}`);
        console.log(`  - 有图片: ${newStatus[0].items_with_pic}`);
        console.log(`  - 无图片: ${newStatus[0].items_without_pic}`);
        console.log(`  - 新增图片: ${newStatus[0].items_with_pic - currentStatus[0].items_with_pic}`);

        // 4. 显示更新后的物品列表
        console.log('\n4️⃣ 显示更新后的物品列表...');
        const [updatedItems] = await connection.execute(`
      SELECT id, name, pic, is_treasure_shop, is_exp_shop
      FROM basic_items 
      WHERE id IN (${Object.keys(ITEMS_PIC_MAPPING).join(',')})
      ORDER BY id
    `);

        console.log('\n📋 更新后的物品列表:');
        updatedItems.forEach(item => {
            const shopInfo = [];
            if (item.is_treasure_shop) shopInfo.push('宝物商店');
            if (item.is_exp_shop) shopInfo.push('经验商店');

            console.log(`  ${item.id}. ${item.name} - 图片:${item.pic} ${shopInfo.length > 0 ? `(${shopInfo.join(', ')})` : ''}`);
        });

        // 5. 检查商店物品状态
        console.log('\n5️⃣ 检查商店物品状态...');
        const [shopStatus] = await connection.execute(`
      SELECT 
        COUNT(CASE WHEN is_treasure_shop = 1 THEN 1 END) as treasure_shop_items,
        COUNT(CASE WHEN is_exp_shop = 1 THEN 1 END) as exp_shop_items,
        COUNT(CASE WHEN is_treasure_shop = 1 AND pic != '' THEN 1 END) as treasure_shop_with_pic,
        COUNT(CASE WHEN is_exp_shop = 1 AND pic != '' THEN 1 END) as exp_shop_with_pic
      FROM basic_items
    `);

        console.log('🏪 商店物品状态:');
        console.log(`  - 宝物商店: ${shopStatus[0].treasure_shop_items} 个物品 (${shopStatus[0].treasure_shop_with_pic} 个有图片)`);
        console.log(`  - 经验商店: ${shopStatus[0].exp_shop_items} 个物品 (${shopStatus[0].exp_shop_with_pic} 个有图片)`);

        console.log('\n🎉 物品图片更新完成！');

    } catch (error) {
        console.error('❌ 更新失败:', error.message);
        if (error.code) {
            console.error('错误代码:', error.code);
        }
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 执行更新
if (require.main === module) {
    updateItemsPicFromStandard()
        .then(() => {
            console.log('\n✅ 物品图片更新脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('\n❌ 物品图片更新脚本执行失败:', error.message);
            process.exit(1);
        });
}

module.exports = { updateItemsPicFromStandard, ITEMS_PIC_MAPPING };
